USE scm_main;
-- 需要先创建拆分字符串存储过程（string_split）
if exists (select 1
          from sysobjects
          where  id = object_id('bd_door_expand_public')
          and type in ('P','PC'))
DROP PROCEDURE bd_door_expand_public
GO
CREATE PROCEDURE bd_door_expand_public
	@rule_no varchar(max),
	@door_bh_str varchar(max),
	@place_serial_str varchar(max),
	@ip varchar(200),
	@operator varchar(200)
as
BEGIN

-- 关闭打印受影响行数
SET NOCOUNT ON;

-- 定义返回受影响行数结果
	declare @result int = 0;
	
-- 开启事务
	BEGIN TRAN

	--  异常扑捉机制
		BEGIN TRY  
		
			-- 创建授权门表
			CREATE TABLE #door_bh(
				  [door_bh] nvarchar(50) COLLATE Chinese_PRC_CI_AS  NULL
			);

			-- 创建授权场所表
			CREATE TABLE #place_serial(
				  [place_serial] nvarchar(50) COLLATE Chinese_PRC_CI_AS  NULL
			);

		--  1.将数据拆分放进各自的临时表
	

			-- 如果门编号为空 需要查询场所下的门
			IF isnull(@door_bh_str,'') = '' BEGIN
				
				insert into #place_serial(place_serial) 
				select items from string_split(@place_serial_str,',');
				
				insert into #door_bh(door_bh)
				select Gate_bh 
				from dt_gate_real r
				inner join #place_serial p on r.Acdep_serial = p.place_serial
			
			END
			-- 如果门编号不为空 只查询当前的门
			ELSE BEGIN

				insert into #door_bh(door_bh) 
				select items from string_split(@door_bh_str,',');

			END;

			--select * from #door_bh
			
			declare @sum int = 0;
			declare @index int = 0;
			declare @door_bh bigint = 0;
			declare @door_name varchar(max) = '';
			declare @dev_serial varchar(200);
			declare @dev_order bigint = 0;
			
			-- 首先清除现有的扩展权限（只用清除一次就可以）
			delete r 
			from MJ_GATE_LEVEL_real r
			inner join #door_bh d on d.door_bh = r.gate_bh 

			select @sum = count(0) from #door_bh

			WHILE (@index < @sum) BEGIN
				
				select @door_bh = isnull(door_bh,'') from #door_bh order by door_bh offset @index rows fetch next 1 rows only

				-- 然后添加扩展权限
				insert into MJ_GATE_LEVEL_real(gate_bh,fx,lx,rule_no,sj,gly_no) VALUES (@door_bh,0,0,@rule_no,GETDATE(),@operator)
				insert into MJ_GATE_LEVEL_real(gate_bh,fx,lx,rule_no,sj,gly_no) VALUES (@door_bh,1,0,@rule_no,GETDATE(),@operator)
				
				-- 查询门关于设备的信息
				select @dev_serial = isnull(dev_serial,''),@dev_order = isnull(dev_order,'') from st_doorfx_real where gate_bh = @door_bh

				-- 通知设备拉取 1073741824 授权的规定格式
				insert into jreal_update_0(jdev_bh,jdodata) VALUES (@dev_serial,1073741824)
				insert into jreal_update_1(jdev_bh,jdodata) VALUES (@dev_serial,1073741824)
				
				-- 查询门的名字
				select @door_name = isnull(door_name,'') from  st_door_real where bh = @door_bh
				
				-- 添加日志
				insert into wt_mj_log_real(lx,log_type,log_state,log_bz,log_sj,log_ip,gly_no,regserial) 
				VALUES (0,3,0,'扩展授权:为'+@door_name+'指定公共权限',GETDATE(),@ip,@operator,'')

				SET @index = @index + 1;
				SET @result = @index;
			END

			

	--  结束异常捕捉
		END TRY

	--  异常处理
		BEGIN CATCH
			ROLLBACK TRAN;
		END CATCH


--  提交事务	
	COMMIT TRAN


--  清除临时表
	select @result as result
	drop table #door_bh
	drop table #place_serial

-- 打开打印受影响行数
SET NOCOUNT OFF;
END;
